﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.IM;

namespace yunbo.Repositorys.IMRepositorys
{
    public class WMS_PDTZRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";
        public int PostWMS_PDTZ(WMS_PDTZ wMS_PDTZ)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[wms_pdtz]([pdtz_code],[pdtz_pdlx],[ckpdxx_code],[ckpd_code],[ck_code],[kq_code],[kw_code],[wl_code],[pdtz_wlmc],[pdtz_wllx],[pdtz_wldw],[pdtz_ggxh],[pdtz_zmkcsl],[pdtz_pdzsl],[pdtz_ykzsl],[pdtz_state])\r\n" +
                    $"values('{wMS_PDTZ.pdtz_code}','{wMS_PDTZ.pdtz_pdlx}','{wMS_PDTZ.ckpdxx_code}','{wMS_PDTZ.ckpd_code}','{wMS_PDTZ.ck_code}','{wMS_PDTZ.kq_code}','{wMS_PDTZ.kw_code}','{wMS_PDTZ.wl_code}','{wMS_PDTZ.pdtz_wlmc}','{wMS_PDTZ.pdtz_wllx}','{wMS_PDTZ.pdtz_wldw}','{wMS_PDTZ.pdtz_ggxh}',{wMS_PDTZ.pdtz_zmkcsl},{wMS_PDTZ.pdtz_pdzsl},{wMS_PDTZ.pdtz_ykzsl},1)";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public FYCXWMS_PDTZ GetCXWMS_PDTZ(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select a.*,b.ck_name,c.kq_name,d.kw_name from [dbo].[wms_pdtz] a\r\n" +
                    $"inner join [dbo].[wbd_ck] b on a.ck_code=b.ck_code\r\n" +
                    $"inner join [dbo].[wbd_kq] c on a.kq_code=c.kq_code\r\n" +
                    $"inner join [dbo].[wbd_kw] d on a.kw_code=d.kw_code\r\n" +
                    $"ORDER BY  pdtz_id \r\n" +
                    $"OFFSET ({PageNumber}-1)*{PageSize} ROWS\r\n" +
                    $"FETCH NEXT {PageSize} ROWS ONLY;";
                List<CXWMS_PDTZ> cXWMS_PDTZs = connection.Query<CXWMS_PDTZ>(sql).ToList();
                string sql1 = $"select COUNT(*) from [dbo].[wms_pdtz] a\r\ninner join [dbo].[wbd_ck] b on a.ck_code=b.ck_code\r\ninner join [dbo].[wbd_kq] c on a.kq_code=c.kq_code\r\ninner join [dbo].[wbd_kw] d on a.kw_code=d.kw_code";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYCXWMS_PDTZ fYCXWMS = new FYCXWMS_PDTZ()
                {
                    cXWMS_PDTZ = cXWMS_PDTZs,
                    zon = zon,
                };
                return fYCXWMS;
            }
        }
        public int UpdateCXWMS_PDTZ(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update wms_pdtz set pdtz_state=2 where pdtz_code='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int UpdateCXWMS_PDTZBH(string code,string bhyy)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update wms_pdtz set pdtz_state=3,pdtz_bhyy='{bhyy}' where pdtz_code='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int DeleteCXWMS_PDTZ(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"delete wms_pdtz where pdtz_code='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
    }
}
